<?php
class ModelCatalogMVDProduct extends Model {
	public function addProduct($data) {
		$this->event->trigger('pre.admin.add.product', $data);
		
		$keys = array('ori_country','shipping_method','prefered_shipping','shipping_cost','vtotal','product_url','wholesale');
		foreach ($keys as $key) {
			$data[$key] = '';
		}

        /*
         * 调试阶段，强制商品类型为“销售组合”的商品状态为“待审批”
         * @author sonicsjh 
         */
        if (2 == $data['product_type']) {
            //$data['status'] = 5;
        }

		//新增商品设置最高排序值
		// $one_month_ago = date('Y-m-d', time()-3600*24*30);
		// $maxOrder = M('product')->where('date_added'>$one_month_ago)->max('sort_order');
		// $data['sort_order'] = $maxOrder+1;

		if($data['newpro'] == 'on'){
			$data['newpro'] = 1;
		}
		else{
			$data['newpro'] = 0;
		}

		if($data['recommend'] == 'on'){
			$data['recommend'] = 1;
		}
		else{
			$data['recommend'] = 0;
		}

		if($data['clearance'] == 'on'){
			$data['clearance'] = 1;
		}
		else{
			$data['clearance'] = 0;
		}

		if($data['promotion'] == 'on'){
			$data['promotion'] = 1;
		}
		else{
			$data['promotion'] = 0;
		}

		$this->db->query("INSERT INTO " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', p_type = '".$this->db->escape($data['p_type'])."', is_part = '".$this->db->escape($data['is_part'])."',is_single = '".$this->db->escape($data['is_single'])."', newpro = '".$data['newpro']."', recommend = '".$data['recommend']."', clearance = '".$data['clearance']."', promotion = '".$data['promotion']."', show_new = '".$data['show_new']."',show_clearance = '".$data['show_clearance']."', product_class1 = '".$this->db->escape($data['product_class1'])."', product_class2 = '".$this->db->escape($data['product_class2'])."', product_class3 = '".$this->db->escape($data['product_class3'])."', product_code = '".$this->db->escape($data['product_code'])."', sku = '" . $this->db->escape($data['sku']) . "', middle_package = '" . $this->db->escape($data['middle_package']) . "',upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "', item_no = '" . $this->db->escape($data['item_no']) . "' ,packing_no = '" . $this->db->escape($data['packing_no']) . "' ,min_purchase_no = '" . $this->db->escape($data['min_purchase_no']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', price = '" . (float)$data['price'] ."', sale_price = '" . (float)$data['sale_price'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . $this->db->escape($data['tax_class_id']) . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW(),`gift_status`='".(int)$data['gift_status']."',`vendor_id`='".(int)$data['vendor']."',`product_type`='".(int)$data['product_type']."'");

		$product_id = $this->db->getLastId();
		
		//mvds
		$this->db->query("INSERT INTO " . DB_PREFIX . "vendor SET  vproduct_id = '" . (int)$product_id . "', ori_country = '" . (int)$data['ori_country'] . "', product_cost = '" . (float)$data['product_cost'] . "', shipping_method = '" . (int)$data['shipping_method'] . "', prefered_shipping = '" . (int)$data['prefered_shipping'] . "', shipping_cost = '" . (float)$data['shipping_cost'] . "', vtotal = '" . (float)$data['vtotal'] . "', product_url = '" . $this->db->escape($data['product_url']) . "', vendor = '" . (int)$data['vendor'] . "', wholesale = '" . $this->db->escape($data['wholesale']) . "', date_add = NOW()");
		//mvde
		
		if (isset($data['image'])) {
			$this->db->query("UPDATE " . DB_PREFIX . "product SET image = '" . $this->db->escape($data['image']) . "' WHERE product_id = '" . (int)$product_id . "'");
		}

		foreach ($data['product_description'] as $language_id => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', description = '" . $this->db->escape($value['description']) . "', tag = '" . $this->db->escape($value['tag']) . "', meta_title = '" . $this->db->escape($value['meta_title']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'");
		}

		if (isset($data['product_store'])) {
			foreach ($data['product_store'] as $store_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = '" . (int)$product_id . "', store_id = '" . (int)$store_id . "'");
			}
		}

		if (isset($data['product_attribute'])) {
			foreach ($data['product_attribute'] as $product_attribute) {
				if ($product_attribute['attribute_id']) {
					$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");

					foreach ($product_attribute['product_attribute_description'] as $language_id => $product_attribute_description) {
						$this->db->query("INSERT INTO " . DB_PREFIX . "product_attribute SET product_id = '" . (int)$product_id . "', attribute_id = '" . (int)$product_attribute['attribute_id'] . "', language_id = '" . (int)$language_id . "', text = '" .  $this->db->escape($product_attribute_description['text']) . "'");
					}
				}
			}
		}

		if (isset($data['product_option'])) {
			foreach ($data['product_option'] as $product_option) {
				if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
					if (isset($product_option['product_option_value'])) {
						$this->db->query("INSERT INTO " . DB_PREFIX . "product_option SET product_id = '" . (int)$product_id . "', option_id = '" . (int)$product_option['option_id'] . "', required = '" . (int)$product_option['required'] . "'");

						$product_option_id = $this->db->getLastId();

						foreach ($product_option['product_option_value'] as $product_option_value) {
							$this->db->query("INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_id = '" . (int)$product_option_id . "', product_id = '" . (int)$product_id . "', option_id = '" . (int)$product_option['option_id'] . "', option_value_id = '" . (int)$product_option_value['option_value_id'] . "', product_code = '".$product_option_value['product_code']."', quantity = '" . (int)$product_option_value['quantity'] . "', subtract = '" . (int)$product_option_value['subtract'] . "', price = '" . (float)$product_option_value['price'] . "', price_prefix = '" . $this->db->escape($product_option_value['price_prefix']) . "', points = '" . (int)$product_option_value['points'] . "', points_prefix = '" . $this->db->escape($product_option_value['points_prefix']) . "', weight = '" . (float)$product_option_value['weight'] . "', weight_prefix = '" . $this->db->escape($product_option_value['weight_prefix']) . "'");
						}
					}
				} else {
					$this->db->query("INSERT INTO " . DB_PREFIX . "product_option SET product_id = '" . (int)$product_id . "', option_id = '" . (int)$product_option['option_id'] . "', value = '" . $this->db->escape($product_option['value']) . "', required = '" . (int)$product_option['required'] . "'");
				}
			}
		}

		if (isset($data['product_discount'])) {
			foreach ($data['product_discount'] as $product_discount) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_discount SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$product_discount['customer_group_id'] . "', quantity = '" . (int)$product_discount['quantity'] . "', priority = '" . (int)$product_discount['priority'] . "', price = '" . (float)$product_discount['price'] . "', date_start = '" . $this->db->escape($product_discount['date_start']) . "', date_end = '" . $this->db->escape($product_discount['date_end']) . "'");
			}
		}

		if (isset($data['product_special'])) {
			foreach ($data['product_special'] as $product_special) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_special SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$product_special['customer_group_id'] . "', priority = '" . (int)$product_special['priority'] . "', price = '" . (float)$product_special['price'] . "', date_start = '" . $this->db->escape($product_special['date_start']) . "', date_end = '" . $this->db->escape($product_special['date_end']) . "'");
			}
		}
		
		//mvds
		if (isset($data['product_shipping'])) {
			foreach ($data['product_shipping'] as $product_shipping) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_shipping SET product_id = '" . (int)$product_id . "', courier_id = '" . (int)$product_shipping['courier_id'] . "', shipping_rate = '" . (float)$product_shipping['shipping_rate'] . "', geo_zone_id = '" . (int)$product_shipping['geo_zone_id'] . "'");
			}
		}
		//mvde

		if (isset($data['product_image'])) {
			foreach ($data['product_image'] as $product_image) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape($product_image['image']) . "', sort_order = '" . (int)$product_image['sort_order'] . "'");
			}
		}

		if (isset($data['product_download'])) {
			foreach ($data['product_download'] as $download_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_download SET product_id = '" . (int)$product_id . "', download_id = '" . (int)$download_id . "'");
			}
		}

		if (isset($data['product_category'])) {
			foreach ($data['product_category'] as $category_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = '" . (int)$product_id . "', category_id = '" . (int)$category_id . "'");
			}
		}
		
		/*添加分配区域*/
		if(isset($data['only_countries']) || isset($data['only_zones']) || isset($data['no_countries']) || isset($data['no_zones'])){
			$this->addArea((int)$product_id,$data);
		}
		/*添加分配区域*/

		if (isset($data['product_filter'])) {
			foreach ($data['product_filter'] as $filter_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_filter SET product_id = '" . (int)$product_id . "', filter_id = '" . (int)$filter_id . "'");
			}
		}

		if (isset($data['product_related'])) {
			foreach ($data['product_related'] as $related_id) {
				$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "' AND related_id = '" . (int)$related_id . "'");
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_related SET product_id = '" . (int)$product_id . "', related_id = '" . (int)$related_id . "'");
				$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$related_id . "' AND related_id = '" . (int)$product_id . "'");
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_related SET product_id = '" . (int)$related_id . "', related_id = '" . (int)$product_id . "'");
			}
		}

		if (isset($data['product_reward'])) {
			foreach ($data['product_reward'] as $customer_group_id => $product_reward) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_reward SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$customer_group_id . "', points = '" . (int)$product_reward['points'] . "'");
			}
		}

		if (isset($data['product_layout'])) {
			foreach ($data['product_layout'] as $store_id => $layout_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_layout SET product_id = '" . (int)$product_id . "', store_id = '" . (int)$store_id . "', layout_id = '" . (int)$layout_id . "'");
			}
		}

		if (isset($data['keyword'])) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'product_id=" . (int)$product_id . "', keyword = '" . $this->db->escape($data['keyword']) . "'");
		}

		if (isset($data['product_recurrings'])) {
			foreach ($data['product_recurrings'] as $recurring) {
				$this->db->query("INSERT INTO `" . DB_PREFIX . "product_recurring` SET `product_id` = " . (int)$product_id . ", customer_group_id = " . (int)$recurring['customer_group_id'] . ", `recurring_id` = " . (int)$recurring['recurring_id']);
			}
		}

        $this->_initProductGroupInfo($product_id, $data);

		$this->cache->delete('product');

		$this->event->trigger('post.admin.add.product', $product_id);

		return $product_id;
	}

	public function editProduct($product_id, $data) {
		$this->event->trigger('pre.admin.edit.product', $data);

        /*
         * 调试阶段，强制商品类型为“销售组合”的商品状态为“待审批”
         * @author sonicsjh 
         */
        if (2 == $data['product_type']) {
            //$data['status'] = 5;
        }

		if($data['newpro'] == 'on'){
			$data['newpro'] = 1;
		}
		else{
			$data['newpro'] = 0;
		}

		if($data['recommend'] == 'on'){
			$data['recommend'] = 1;
		}
		else{
			$data['recommend'] = 0;
		}

		if($data['clearance'] == 'on'){
			$data['clearance'] = 1;
		}
		else{
			$data['clearance'] = 0;
		}

		if($data['promotion'] == 'on'){
			$data['promotion'] = 1;
		}
		else{
			$data['promotion'] = 0;
		}



        $update_sql = ("UPDATE " . DB_PREFIX . "product SET model = '" . $this->db->escape($data['model']) . "', p_type = '".$this->db->escape($data['p_type'])."', is_single = '".$this->db->escape($data['is_single'])."',is_part = '".$this->db->escape($data['is_part'])."', newpro = '".$data['newpro']."', recommend = '".$data['recommend']."', clearance = '".$data['clearance']."', promotion = '".$data['promotion']."', show_new = '".$data['show_new']."',show_clearance = '".$data['show_clearance']."', product_class1 = '".$this->db->escape($data['product_class1'])."', product_class2 = '".$this->db->escape($data['product_class2'])."',product_class3 = '".$this->db->escape($data['product_class3'])."', product_code = '".$this->db->escape($data['product_code'])."', sku = '" . $this->db->escape($data['sku']) . "', middle_package = '" . $this->db->escape($data['middle_package']) . "',upc = '" . $this->db->escape($data['upc']) . "', ean = '" . $this->db->escape($data['ean']) . "' ,item_no = '" . $this->db->escape($data['item_no']) . "' ,packing_no = '" . $this->db->escape($data['packing_no']) . "' ,min_purchase_no = '" . $this->db->escape($data['min_purchase_no']) . "', jan = '" . $this->db->escape($data['jan']) . "', isbn = '" . $this->db->escape($data['isbn']) . "', mpn = '" . $this->db->escape($data['mpn']) . "', location = '" . $this->db->escape($data['location']) . "', quantity = '" . (int)$data['quantity'] . "', minimum = '" . (int)$data['minimum'] . "', addnum = '".(int)$data['addnum']."', subtract = '" . (int)$data['subtract'] . "', stock_status_id = '" . (int)$data['stock_status_id'] . "', date_available = '" . $this->db->escape($data['date_available']) . "', manufacturer_id = '" . (int)$data['manufacturer_id'] . "', shipping = '" . (int)$data['shipping'] . "', points = '" . (int)$data['points'] . "', weight = '" . (float)$data['weight'] . "', weight_class_id = '" . (int)$data['weight_class_id'] . "', length = '" . (float)$data['length'] . "', width = '" . (float)$data['width'] . "', height = '" . (float)$data['height'] . "', length_class_id = '" . (int)$data['length_class_id'] . "', status = '" . (int)$data['status'] . "', tax_class_id = '" . $this->db->escape($data['tax_class_id']) . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW(), gift_status = '".(int)$data['gift_status']."',`product_type`='".(int)$data['product_type']."',`vendor_id`='".(int)$data['vendor']."',`lack_reason`='".$data['lack_reason']."',`lack_status`='".(int)$data['lack_status']."',`sale_price`='".$data['sale_price']."' WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query($update_sql);

		
			$this->db->query("UPDATE " . DB_PREFIX . "product SET price = '" . (float)$data['price'] . "' WHERE product_id = '" . (int)$product_id . "'");
		

			
		
		//mvds
		
		$this->db->query("DELETE FROM " . DB_PREFIX . "vendor WHERE vproduct_id = '" . (int)$product_id . "'");
		$this->db->query("INSERT INTO " . DB_PREFIX . "vendor SET  vproduct_id = '" . (int)$product_id . "', ori_country = '" . (int)$data['ori_country'] . "', product_cost = '" . (float)$data['product_cost'] . "', shipping_method = '" . (int)$data['shipping_method'] . "', prefered_shipping = '" . (int)$data['prefered_shipping'] . "', shipping_cost = '" . (float)$data['shipping_cost'] . "', vtotal = '" . (float)$data['vtotal'] . "', product_url = '" . $this->db->escape($data['product_url']) . "', vendor = '" . (int)$data['vendor'] . "', wholesale = '" . $this->db->escape($data['wholesale']) . "', date_add = NOW()");
		//mvde
		
		if (isset($data['image'])) {
			$this->db->query("UPDATE " . DB_PREFIX . "product SET image = '" . $this->db->escape($data['image']) . "' WHERE product_id = '" . (int)$product_id . "'");
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

		foreach ($data['product_description'] as $language_id => $value) {
			$number = (int)$this->db->escape($value['name']);
			// var_dump($this->db->escape($value['name']));
			// var_dump($number);die();
			$pname = $this->db->escape($value['name']);
			$pmeta_title = $this->db->escape($value['meta_title']);
			/*if ($number) {
				// echo 11;
				$pname = substr($pname, 2);
				$pmeta_title = substr($pmeta_title, 2);
				// var_dump()
			}
			// var_dump($pname);die();
			if ($data['lack_status']==2) {
				$pname = '2D'.$pname;
				$pmeta_title = '2D'.$pmeta_title;
			}elseif ($data['lack_status']==3) {
				$pname = '3I'.$pname;
				$pmeta_title = '3I'.$pmeta_title;
			}else{
				$pname = $pname;
				$pmeta_title = $pmeta_title;
			}*/

			$this->db->query("INSERT INTO " . DB_PREFIX . "product_description SET product_id = '" . (int)$product_id . "', language_id = '" . (int)$language_id . "', name = '" . $pname . "', description = '" . $this->db->escape($value['description']) . "', tag = '" . $this->db->escape($value['tag']) . "', meta_title = '" . $pmeta_title . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'");
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_store'])) {
			foreach ($data['product_store'] as $store_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_store SET product_id = '" . (int)$product_id . "', store_id = '" . (int)$store_id . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "'");

		if (!empty($data['product_attribute'])) {
			foreach ($data['product_attribute'] as $product_attribute) {
				if ($product_attribute['attribute_id']) {
					$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");

					foreach ($product_attribute['product_attribute_description'] as $language_id => $product_attribute_description) {
						$this->db->query("INSERT INTO " . DB_PREFIX . "product_attribute SET product_id = '" . (int)$product_id . "', attribute_id = '" . (int)$product_attribute['attribute_id'] . "', language_id = '" . (int)$language_id . "', text = '" .  $this->db->escape($product_attribute_description['text']) . "'");
					}
				}
			}
		}

		

		if (isset($data['product_option'])) {
			foreach ($data['product_option'] as $product_option) {
				if (true||$product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
					if (isset($product_option['product_option_value'])) {
						$this->db->query("DELETE FROM " . DB_PREFIX . "product_option WHERE product_id = '" . (int)$product_id . "'");
						$this->db->query("DELETE FROM " . DB_PREFIX . "product_option_value WHERE product_id = '" . (int)$product_id . "'");
						$this->db->query("INSERT INTO " . DB_PREFIX . "product_option SET product_option_id = '" . (int)$product_option['product_option_id'] . "', product_id = '" . (int)$product_id . "', option_id = '" . (int)$product_option['option_id'] . "', required = '" . (int)$product_option['required'] . "'");

						$product_option_id = $this->db->getLastId();

						foreach ($product_option['product_option_value'] as $product_option_value) {
							$this->db->query("INSERT INTO " . DB_PREFIX . "product_option_value SET product_option_value_id = '" . (int)$product_option_value['product_option_value_id'] . "', product_option_id = '" . (int)$product_option_id . "', product_id = '" . (int)$product_id . "', option_id = '" . (int)$product_option['option_id'] . "', option_value_id = '" . (int)$product_option_value['option_value_id'] ."', product_code = '".$product_option_value['product_code']."', quantity = '" . (int)$product_option_value['quantity'] . "', subtract = '" . (int)$product_option_value['subtract'] . "', price = '" . (float)$product_option_value['price'] . "', price_prefix = '" . $this->db->escape($product_option_value['price_prefix']) . "', points = '" . (int)$product_option_value['points'] . "', points_prefix = '" . $this->db->escape($product_option_value['points_prefix']) . "', weight = '" . (float)$product_option_value['weight'] . "', weight_prefix = '" . $this->db->escape($product_option_value['weight_prefix']) . "'");
						}
					}
				} else {
					$this->db->query("INSERT INTO " . DB_PREFIX . "product_option SET product_option_id = '" . (int)$product_option['product_option_id'] . "', product_id = '" . (int)$product_id . "', option_id = '" . (int)$product_option['option_id'] . "', value = '" . $this->db->escape($product_option['value']) . "', required = '" . (int)$product_option['required'] . "'");
				}
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_discount'])) {
			foreach ($data['product_discount'] as $product_discount) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_discount SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$product_discount['customer_group_id'] . "', quantity = '" . (int)$product_discount['quantity'] . "', priority = '" . (int)$product_discount['priority'] . "', price = '" . (float)$product_discount['price'] . "', date_start = '" . $this->db->escape($product_discount['date_start']) . "', date_end = '" . $this->db->escape($product_discount['date_end']) . "'");
			}
		}
		
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_special'])) {
			foreach ($data['product_special'] as $product_special) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_special SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$product_special['customer_group_id'] . "', priority = '" . (int)$product_special['priority'] . "', price = '" . (float)$product_special['price'] . "', date_start = '" . $this->db->escape($product_special['date_start']) . "', date_end = '" . $this->db->escape($product_special['date_end']) . "'");
			}
		}
		
		//mvds
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_shipping WHERE product_id = '" . (int)$product_id . "'");		
		if (isset($data['product_shipping'])) {
			foreach ($data['product_shipping'] as $product_shipping) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_shipping SET product_id = '" . (int)$product_id . "', courier_id = '" . (int)$product_shipping['courier_id'] . "', shipping_rate = '" . (float)$product_shipping['shipping_rate'] . "', geo_zone_id = '" . (int)$product_shipping['geo_zone_id'] . "'");
			}
		}
		//mvde

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_image'])) {
			foreach ($data['product_image'] as $product_image) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_image SET product_id = '" . (int)$product_id . "', image = '" . $this->db->escape($product_image['image']) . "', sort_order = '" . (int)$product_image['sort_order'] . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_download WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_download'])) {
			foreach ($data['product_download'] as $download_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_download SET product_id = '" . (int)$product_id . "', download_id = '" . (int)$download_id . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_category'])) {
			foreach ($data['product_category'] as $category_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = '" . (int)$product_id . "', category_id = '" . (int)$category_id . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_filter WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_filter'])) {
			foreach ($data['product_filter'] as $filter_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_filter SET product_id = '" . (int)$product_id . "', filter_id = '" . (int)$filter_id . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE related_id = '" . (int)$product_id . "'");

		if (isset($data['product_related'])) {
			foreach ($data['product_related'] as $related_id) {
				$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "' AND related_id = '" . (int)$related_id . "'");
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_related SET product_id = '" . (int)$product_id . "', related_id = '" . (int)$related_id . "'");
				$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$related_id . "' AND related_id = '" . (int)$product_id . "'");
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_related SET product_id = '" . (int)$related_id . "', related_id = '" . (int)$product_id . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_reward WHERE product_id = '" . (int)$product_id . "'");

		if (isset($data['product_reward'])) {
			foreach ($data['product_reward'] as $customer_group_id => $value) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_reward SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$customer_group_id . "', points = '" . (int)$value['points'] . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");
		
		/*编辑分配区域*/
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_area WHERE product_id = '" . (int)$product_id . "'");
		
		if(isset($data['only_countries']) || isset($data['only_zones']) || isset($data['no_countries']) || isset($data['no_zones'])){
			$this->addArea((int)$product_id,$data);
		}
		/*编辑分配区域*/

		if (isset($data['product_layout'])) {
			foreach ($data['product_layout'] as $store_id => $layout_id) {
				$this->db->query("INSERT INTO " . DB_PREFIX . "product_to_layout SET product_id = '" . (int)$product_id . "', store_id = '" . (int)$store_id . "', layout_id = '" . (int)$layout_id . "'");
			}
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "'");

		if ($data['keyword']) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'product_id=" . (int)$product_id . "', keyword = '" . $this->db->escape($data['keyword']) . "'");
		}

		$this->db->query("DELETE FROM `" . DB_PREFIX . "product_recurring` WHERE product_id = " . (int)$product_id);

		if (isset($data['product_recurrings'])) {
			foreach ($data['product_recurrings'] as $recurring) {
				$this->db->query("INSERT INTO `" . DB_PREFIX . "product_recurring` SET `product_id` = " . (int)$product_id . ", customer_group_id = " . (int)$recurring['customer_group_id'] . ", `recurring_id` = " . (int)$recurring['recurring_id']);
			}
		}
		$productcode = $data['product_code'].'0';
		$inveninfo = M("inventory")->where(array('product_code'=>$productcode,'warehouse_id'=>4))->find();
		if (empty($inveninfo)) {
			$data = array(
				'product_code'=>$productcode,
				'warehouse_id'=>4,
				'safe_quantity'=>$data['safe_quantity'],
				'date_added'=>date('Y-m-d H:i:s'),
			);
			M('inventory')->add($data);
		}else{
			$sql = "UPDATE " . DB_PREFIX . "inventory SET safe_quantity = '" . (float)$data['safe_quantity'] . "' WHERE inventory.warehouse_id =4 AND product_code = '" .$productcode . "'";
			// echo $sql;die();
			$this->db->query($sql);
		}

        $this->_initProductGroupInfo($product_id, $data);

		$this->cache->delete('product');

		$this->event->trigger('post.admin.edit.product', $product_id);
	}

	public function getProHistory($product_id){
		return M('product_history')->where(array('product_id'=>$product_id))->limit(0,4)->order('date_added desc')->select();
		
	}

	public function getInventory($product_id){
		$product_code = M('product')->where(array('product_id'=>$product_id))->getField('product_code');
		$product_code = $product_code.'0';
		$sql = "SELECT i.safe_quantity FROM inventory AS i WHERE i.warehouse_id =4 AND i.product_code = ".$product_code." limit 1";
		$query = $this->db->query($sql);
		return $query->row['safe_quantity'];
	}

	public function addStHistory($product_id, $user_id,$comments){
		$username = M('user')->where(array('user_id'=>$user_id))->getField('username');
		$data = array(
					'product_id'=>$product_id, 
		  			'user_id'=>$user_id,
		  			'operator_name'=>$username,
		  		 	'comment'=>$comments, 
					'date_added'=>date('Y-m-d H:i:s'));
		M('product_history')->data($data)->add();
	}

	public function copyProduct($product_id) {
		//mvds
		$query = $this->db->query("SELECT DISTINCT *, p.sort_order as psort_order FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");
		//mvde
		
		if ($query->num_rows) {
			$data = array();

			$data = $query->row;

			$data['sku'] = '';
			$data['upc'] = '';
			$data['viewed'] = '0';
			$data['keyword'] = '';
			$data['status'] = '0';
			//mvds
			$data['sort_order'] = $query->row['psort_order'];
			//mvde

			$data = array_merge($data, array('product_attribute' => $this->getProductAttributes($product_id)));
			$data = array_merge($data, array('product_description' => $this->getProductDescriptions($product_id)));
			$data = array_merge($data, array('product_discount' => $this->getProductDiscounts($product_id)));
			$data = array_merge($data, array('product_filter' => $this->getProductFilters($product_id)));
			$data = array_merge($data, array('product_image' => $this->getProductImages($product_id)));
			$data = array_merge($data, array('product_option' => $this->getProductOptions($product_id)));
			$data = array_merge($data, array('product_related' => $this->getProductRelated($product_id)));
			$data = array_merge($data, array('product_reward' => $this->getProductRewards($product_id)));
			$data = array_merge($data, array('product_special' => $this->getProductSpecials($product_id)));
			$data = array_merge($data, array('product_category' => $this->getProductCategories($product_id)));
			$data = array_merge($data, array('product_download' => $this->getProductDownloads($product_id)));
			$data = array_merge($data, array('product_layout' => $this->getProductLayouts($product_id)));
			$data = array_merge($data, array('product_store' => $this->getProductStores($product_id)));
			$data = array_merge($data, array('product_recurrings' => $this->getRecurrings($product_id)));

            $data = array_merge($data, array('groupChild' => $this->_getGroupChildByParentProductId($product_id, true)));

            $this->addProduct($data);
		}
	}

	public function deleteProduct($product_id) {
		$this->event->trigger('pre.admin.delete.product', $product_id);

		$this->db->query("DELETE FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");
		//mvds
		$this->db->query("DELETE FROM " . DB_PREFIX . "vendor WHERE vproduct_id = '" . (int)$product_id . "'");
		//mvde
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_filter WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_option WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_option_value WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_related WHERE related_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_reward WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_download WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "review WHERE product_id = '" . (int)$product_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "product_recurring WHERE product_id = " . (int)$product_id);
		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "'");

        $this->_cleanProductGroupByParentProductId($product_id);

		$this->cache->delete('product');

		$this->event->trigger('post.admin.delete.product', $product_id);
	}

	public function getProduct($product_id) {
		//$query = $this->db->query("SELECT DISTINCT *, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "') AS keyword FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");
		//mvds
		$query = $this->db->query("SELECT DISTINCT *,p.is_shelf_product, p.sort_order AS psort_order, CONCAT(vds.firstname, ' ', vds.lastname) AS vname, CONCAT(vds.city, ',', vds.address_1, ',', vds.postcode) AS address,(SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'product_id=" . (int)$product_id . "') AS keyword FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id) WHERE p.product_id = '" . (int)$product_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'");
        $ret = $query->row;
		//mvde
        $ret['groupChild'] = $this->_getGroupChildByParentProductId($product_id);
		return $ret;
	}

	public function getProducts($data = array()) {
		$data = $this->trim_all_arr($data);

		//$sql = "SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
		//mvds
		$sql = "SELECT *,p.status as status FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id)"; 
		//mvde
		
		$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'"; 
		
		if (!empty($data['filter_name'])) {
			$sql .= " AND pd.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
		}
		
		//mvds
		if (!empty($data['filter_sku'])) {
			$sql .= " AND LCASE(p.sku) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_sku'])) . "%'";
		}
		
		//商品编码
		if (!empty($data['filter_product_code'])) {
			$sql .= " AND LCASE(p.product_code) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_product_code'])) . "%'";
		}
			
		if (isset($data['filter_vendor']) && !is_null($data['filter_vendor'])) {
			$sql .= " AND vd.vendor = '" . (int)$data['filter_vendor'] . "'";
		}
			
		if (isset($data['filter_vendor_name']) && !is_null($data['filter_vendor_name'])) {
			$sql .= " AND vds.vendor_id = '" . (int)$data['filter_vendor_name'] . "'";
		}
		//mvde

		if (!empty($data['filter_model'])) {
			$sql .= " AND p.model LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
		}

		if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
			$sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
		}

		if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
			$sql .= " AND p.quantity = '" . (int)$data['filter_quantity'] . "'";
		}

		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
		}
		
		//是否有库存
		if(isset($data['filter_stock'])){
			if($data['filter_stock'] == 1){
				$sql .= " AND EXISTS (SELECT I.`id` from `inventory` AS I LEFT JOIN `warehouse` AS W ON (I.warehouse_id=W.warehouse_id) WHERE I.`product_code`=CONCAT(p.`product_code`,0) AND I.`available_quantity`>0 AND W.`type`<3)";
			}
			else{
				$sql .= " AND (EXISTS (SELECT I1.`id` from `inventory` AS I1 LEFT JOIN `warehouse` AS W ON (I1.`warehouse_id`=W.`warehouse_id`) WHERE I1.`product_code`=CONCAT(p.`product_code`,0) AND W.`type`<3 GROUP BY I1.`product_code` HAVING SUM(`available_quantity`)=0) OR NOT EXISTS (SELECT `id` from `inventory` AS I2 WHERE I2.`product_code`=CONCAT(p.`product_code`,0)))";
			}
		}

		$sql .= " GROUP BY p.product_id";

		$sort_data = array(
			'pd.name',
			'p.model',
			//mvds
			'p.sku',
			'vds.vendor_id',
			//mvde
			'p.price',
			'p.quantity',
			'p.status',
			'p.sort_order',
			'p.date_added'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY pd.name";
		}
		
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}
		$query = $this->db->query($sql);
		//echo $sql;die();
		return $query->rows;
	}

	
	public function getoptionvalue($product_id){
		$sql = "SELECT product_option_value.quantity FROM product_option_value  WHERE  product_option_value.product_id = " . (int)$product_id;
		
		$query = $this->db->query($sql);
		$qty = 0;
		foreach ($query->rows as $key => $value) {
			$qty+=$value['quantity'];
		}
		return $qty;
	}


	public function getProductsByCategoryId($category_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . (int)$category_id . "' ORDER BY pd.name ASC");

		return $query->rows;
	}

	public function getProductDescriptions($product_id) {
		$product_description_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_description WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_description_data[$result['language_id']] = array(
				'name'             => $result['name'],
				'description'      => $result['description'],
				'meta_title'       => $result['meta_title'],
				'meta_description' => $result['meta_description'],
				'meta_keyword'     => $result['meta_keyword'],
				'tag'              => $result['tag']
			);
		}

		return $product_description_data;
	}

	public function getProductCategories($product_id) {
		$product_category_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_category_data[] = $result['category_id'];
		}

		return $product_category_data;
	}

	public function getProductFilters($product_id) {
		$product_filter_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_filter WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_filter_data[] = $result['filter_id'];
		}

		return $product_filter_data;
	}

	public function getProductAttributes($product_id) {
		$product_attribute_data = array();

		$product_attribute_query = $this->db->query("SELECT attribute_id FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' GROUP BY attribute_id");

		foreach ($product_attribute_query->rows as $product_attribute) {
			$product_attribute_description_data = array();

			$product_attribute_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");

			foreach ($product_attribute_description_query->rows as $product_attribute_description) {
				$product_attribute_description_data[$product_attribute_description['language_id']] = array('text' => $product_attribute_description['text']);
			}

			$product_attribute_data[] = array(
				'attribute_id'                  => $product_attribute['attribute_id'],
				'product_attribute_description' => $product_attribute_description_data
			);
		}

		return $product_attribute_data;
	}

	public function getProductOptions($product_id) {
		$product_option_data = array();

		$product_option_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_option` po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN `" . DB_PREFIX . "option_description` od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "'");

		foreach ($product_option_query->rows as $product_option) {
			$product_option_value_data = array();

			$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "'");

			foreach ($product_option_value_query->rows as $product_option_value) {
				$product_option_value_data[] = array(
					'product_option_value_id' => $product_option_value['product_option_value_id'],
					'option_value_id'         => $product_option_value['option_value_id'],
					'product_code'                => $product_option_value['product_code'],
					'quantity'                => $product_option_value['quantity'],
					'subtract'                => $product_option_value['subtract'],
					'price'                   => $product_option_value['price'],
					'price_prefix'            => $product_option_value['price_prefix'],
					'points'                  => $product_option_value['points'],
					'points_prefix'           => $product_option_value['points_prefix'],
					'weight'                  => $product_option_value['weight'],
					'weight_prefix'           => $product_option_value['weight_prefix']
				);
			}

			$product_option_data[] = array(
				'product_option_id'    => $product_option['product_option_id'],
				'product_option_value' => $product_option_value_data,
				'option_id'            => $product_option['option_id'],
				'name'                 => $product_option['name'],
				'type'                 => $product_option['type'],
				'value'                => $product_option['value'],
				'required'             => $product_option['required']
			);
		}

		return $product_option_data;
	}

	public function getProductImages($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order ASC");

		return $query->rows;
	}

	public function getProductDiscounts($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' ORDER BY quantity, priority, price");

		return $query->rows;
	}

	public function getProductSpecials($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' ORDER BY priority, price");

		return $query->rows;
	}

	public function getgroupProductSpecials($product_id,$user_group_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_special WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = ".$user_group_id." ORDER BY priority, price");

		return $query->rows;
	}
	public function getProductRewards($product_id) {
		$product_reward_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_reward WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_reward_data[$result['customer_group_id']] = array('points' => $result['points']);
		}

		return $product_reward_data;
	}

	public function getProductDownloads($product_id) {
		$product_download_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_download WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_download_data[] = $result['download_id'];
		}

		return $product_download_data;
	}

	public function getProductStores($product_id) {
		$product_store_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_store WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_store_data[] = $result['store_id'];
		}

		return $product_store_data;
	}

	public function getProductLayouts($product_id) {
		$product_layout_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_layout_data[$result['store_id']] = $result['layout_id'];
		}

		return $product_layout_data;
	}

	public function getProductRelated($product_id) {
		$product_related_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related WHERE product_id = '" . (int)$product_id . "'");

		foreach ($query->rows as $result) {
			$product_related_data[] = $result['related_id'];
		}

		return $product_related_data;
	}

	public function getRecurrings($product_id) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "product_recurring` WHERE product_id = '" . (int)$product_id . "'");

		return $query->rows;
	}

	public function getTotalProducts($data = array()) {
		$data = $this->trim_all_arr($data);
		//$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)";
		//mvds
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id)";
		//mvde
		
		$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

		if (!empty($data['filter_name'])) {
			$sql .= " AND pd.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
		}

		//商品编码
		if (!empty($data['filter_product_code'])) {
			$sql .= " AND LCASE(p.product_code) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_product_code'])) . "%'";
		}
		
		//mvds
		if (!empty($data['filter_sku'])) {
			$sql .= " AND LCASE(p.sku) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_sku'])) . "%'";
		}
			
		if (isset($data['filter_vendor']) && !is_null($data['filter_vendor'])) {
			$sql .= " AND vd.vendor = '" . (int)$this->db->escape($data['filter_vendor']) . "'";
		}
			
		if (isset($data['filter_vendor_name']) && !is_null($data['filter_vendor_name'])) {
			$sql .= " AND vds.vendor_id = '" . (int)$this->db->escape($data['filter_vendor_name']) . "'";
		}
		//mvde

		if (!empty($data['filter_model'])) {
			$sql .= " AND p.model LIKE '%" . $this->db->escape($data['filter_model']) . "%'";
		}

		if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
			$sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
		}

		if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
			$sql .= " AND p.quantity = '" . (int)$data['filter_quantity'] . "'";
		}

		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
		}

		//是否有库存
		if(isset($data['filter_stock'])){
			if($data['filter_stock'] == 1){
				$sql .= " AND EXISTS (SELECT I.`id` from `inventory` AS I LEFT JOIN `warehouse` AS W ON (I.warehouse_id=W.warehouse_id) WHERE I.`product_code`=CONCAT(p.`product_code`,0) AND I.`available_quantity`>0 AND W.`type`<3)";
			}
			else{
				$sql .= " AND (EXISTS (SELECT I1.`id` from `inventory` AS I1 LEFT JOIN `warehouse` AS W ON (I1.`warehouse_id`=W.`warehouse_id`) WHERE I1.`product_code`=CONCAT(p.`product_code`,0) AND W.`type`<3 GROUP BY I1.`product_code` HAVING SUM(`available_quantity`)=0) OR NOT EXISTS (SELECT `id` from `inventory` AS I2 WHERE I2.`product_code`=CONCAT(p.`product_code`,0)))";
			}
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getTotalProductsByTaxClassId($tax_class_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE tax_class_id = '" . (int)$tax_class_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByStockStatusId($stock_status_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE stock_status_id = '" . (int)$stock_status_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByWeightClassId($weight_class_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE weight_class_id = '" . (int)$weight_class_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByLengthClassId($length_class_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE length_class_id = '" . (int)$length_class_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByDownloadId($download_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_download WHERE download_id = '" . (int)$download_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByManufacturerId($manufacturer_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE manufacturer_id = '" . (int)$manufacturer_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByAttributeId($attribute_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_attribute WHERE attribute_id = '" . (int)$attribute_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByOptionId($option_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_option WHERE option_id = '" . (int)$option_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByProfileId($recurring_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_recurring WHERE recurring_id = '" . (int)$recurring_id . "'");

		return $query->row['total'];
	}

	public function getTotalProductsByLayoutId($layout_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_layout WHERE layout_id = '" . (int)$layout_id . "'");

		return $query->row['total'];
	}
	
	//mvds
	public function getVendorsByVendorId($vendor_id) {
		$data = array();
		$query = $this->db->query("SELECT *, CONCAT(v.firstname, ' ',v.lastname) AS vname, CONCAT(v.city, ',', v.address_1, ',', v.postcode) AS address FROM " . DB_PREFIX . "vendors v WHERE v.vendor_id = '" . (int)$vendor_id . "'");
		$data = $query->row;
				
		if ($query->num_rows) {
			$data = array_merge($data,array('country_name' => $this->getCountryName($data['country_id'])));
			if ($this->getZoneName($data['zone_id'])) {
				$data = array_merge($data,array('zone_name' => $this->getZoneName($data['zone_id'])));
			} else {
				$data = array_merge($data,array('zone_name' => array(0 => $this->language->get('text_none'))));
			}
		}
		return $data;
	}
	
	public function getVendors($data = array()) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "vendors v ORDER BY v.vendor_name");
		$vendors_data = $query->rows;
		return $vendors_data;
		$this->cache->set('vendors', $vendors_data);
	}
			
	public function getCountryName($country_id) {
		$country_name = array();
		$query = $this->db->query("SELECT name AS CountryName FROM " . DB_PREFIX . "country c WHERE c.country_id = '" . (int)$country_id . "'");
			foreach ($query->rows as $result) {
				$country_name[] = $result['CountryName'];
			}
		return $country_name;
	}
			
	public function getZoneName($zone_id) {
		$zone_name = array();
		$query = $this->db->query("SELECT name AS ZoneName FROM " . DB_PREFIX . "zone z WHERE z.zone_id = '" . (int)$zone_id . "'");
				
		foreach ($query->rows as $result) {
			$zone_name[] = $result['ZoneName'];
		}
		return $zone_name;
	}
			
	public function getCountry($data = array()) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "country c ORDER BY c.name");
		$country_data = $query->rows;
		return $country_data;
		$this->cache->set('product', $country_data);
	}
			
	public function getCourier($data = array()) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "courier cr ORDER BY cr.courier_name");
		$couriers_data = $query->rows;
		return $couriers_data;
		$this->cache->set('product', $couriers_data);
	}
			
	public function getTotalProductsByVendor($data = array(),$vendor_id) {
		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN " . DB_PREFIX . "vendors vds ON (vd.vendor = vds.vendor_id) WHERE vds.vendor_id = '" . (int)$vendor_id . "' AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";
				
		if (isset($data['filter_name']) && !is_null($data['filter_name'])) {
			$sql .= " AND LCASE(pd.name) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_name'], 'UTF-8')) . "%'";
		}
		
		//mvds
		if (isset($data['filter_sku']) && !is_null($data['filter_sku'])) {
			$sql .= " AND LCASE(p.sku) LIKE '" . $this->db->escape(mb_strtolower($data['filter_sku'])) . "%'";
		}
			
		if (isset($data['filter_vendor']) && !is_null($data['filter_vendor'])) {
			$sql .= " AND vd.vendor = '" . (int)$this->db->escape($data['filter_vendor']) . "'";
		}
			
		if (isset($data['filter_vendor_name']) && !is_null($data['filter_vendor_name'])) {
			$sql .= " AND vds.vendor_id = '" . (int)$this->db->escape($data['filter_vendor_name']) . "'";
		}
		//mvde

		if (isset($data['filter_model']) && !is_null($data['filter_model'])) {
			$sql .= " AND LCASE(p.model) LIKE '%" . $this->db->escape(mb_strtolower($data['filter_model'], 'UTF-8')) . "%'";
		}
				
		if (isset($data['filter_price']) && !is_null($data['filter_price'])) {
			$sql .= " AND p.price LIKE '" . $this->db->escape($data['filter_price']) . "%'";
		}
				
		if (isset($data['filter_quantity']) && !is_null($data['filter_quantity'])) {
			$sql .= " AND p.quantity = '" . $this->db->escape($data['filter_quantity']) . "'";
		}
				
		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$sql .= " AND p.status = '" . (int)$data['filter_status'] . "'";
		}

		$query = $this->db->query($sql);
		return $query->row['total'];
	}	

	public function getTotalWaitingApprovalProduct($data = array()) {
		$query = $this->db->query("SELECT count(*) AS total FROM " . DB_PREFIX . "product p WHERE p.status = '5'");
		return $query->row['total'];
	}			
						
	public function getProductShippings($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_shipping WHERE product_id = '" . (int)$product_id . "' ORDER BY product_shipping_id");
		return $query->rows;
	}
			
	public function getVendorData($vendor_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "vendors WHERE vendor_id = '" . (int)$this->db->escape($vendor_id) . "'");
		return $query->row;
	}
	//mvde
	public function stickSort($product_id){
		$one_month_ago = date('Y-m-d', time()-3600*24*30);
		$maxOrder = M('product')->where('date_added'>$one_month_ago)->max('sort_order');
		$data['sort_order'] = $maxOrder+1;
		$map['product_id'] = $product_id;
		$productModel = M('product');
		$product_data = $productModel
		->where($map)
		->data($data)
		->save();
		return $product_data;
	}

	public function getAllProductsDetail($filter){

		// $productoptionvalueModel = M('product_option_value');

		// $product_data = $productoptionvalueModel
		// ->alias('pov')
		// ->join('LEFT join option_description od ON od.option_id=pov.option_id')
		// ->join('LEFT join product p ON pov.product_id=p.product_id')
		// ->join('LEFT join product_description pd ON pd.product_id=pov.product_id')
		// ->join('LEFT join vendor v ON v.vproduct_id=pov.product_id')
		// ->join('LEFT join vendors vs ON vs.vendor_id=v.vendor')
		// ->join('LEFT join option_value_description ovd ON ovd.option_value_id=pov.option_value_id')
		// ->join('LEFT join product_to_category ptc ON ptc.product_id=pov.product_id')
		// ->join('LEFT join category_description cd ON cd.category_id=ptc.category_id')
		// ->group('pov.product_id,ovd.name')
		// ->field('pov.product_id,p.status,vs.lastname,vs.vendor_name,pd.name,p.item_no,p.packing_no,p.min_purchase_no,p.quantity,v.product_cost,p.price,p.sku,p.length,p.height,p.width,p.weight,p.minimum,p.sort_order,p.date_added,ovd.name as ovd_name,cd.name as cd_name')
		// ->select();

		$productoptionvalueModel1 = M('product_option_value');
		
		$product_data1 = $productoptionvalueModel1
		->alias('pov')
		->join('LEFT join option_description od ON od.option_id=pov.option_id')
		->join('LEFT join product p ON pov.product_id=p.product_id')
		->join('LEFT join manufacturer ma ON ma.manufacturer_id=p.manufacturer_id')
		->join('LEFT join product_description pd ON pd.product_id=pov.product_id')
		->join('LEFT join vendor v ON v.vproduct_id=pov.product_id')
		->join('LEFT join vendors vs ON vs.vendor_id=v.vendor')
		->join('LEFT join option_value_description ovd ON ovd.option_value_id=pov.option_value_id')
		->join('LEFT join category c1 ON c1.category_id=p.product_class1')
		->join('LEFT join category c2 ON c2.category_id=p.product_class2')
		->join('LEFT join category c3 ON c3.category_id=p.product_class3')
		->join('LEFT join manufacturer m ON m.manufacturer_id=p.manufacturer_id')
		->where($filter)
		->group('pov.product_id,ovd.name')
		->field('p.product_type,pov.option_value_id,pov.product_id,p.status,vs.lastname,vs.vendor_name,pd.name,p.p_type,p.is_single,is_part,p.item_no,p.packing_no,p.min_purchase_no,pov.quantity,v.product_cost,p.price,p.model,p.product_code,p.sku,p.middle_package,p.length,p.height,p.width,p.weight,p.minimum,p.image,p.sale_price,p.is_shelf_product,p.sort_order,p.date_added,p.clearance,ovd.name as ovd_name,c1.code as product_class1,c2.code as product_class2,c3.code as product_class3,m.name as mname,ma.name as maname')
		->select();
		$productoptionvalueModel2 = M('product');
		$filter['_string'] = "pov.product_id is null";
		$product_data2 = $productoptionvalueModel2
		->alias('p')
		->join('LEFT JOIN product_option_value pov ON pov.product_id=p.product_id')
		->join('LEFT join product_description pd ON pd.product_id=p.product_id')
		->join('LEFT join manufacturer ma ON ma.manufacturer_id=p.manufacturer_id')
		->join('LEFT join vendor v ON v.vproduct_id=p.product_id')
		->join('LEFT join vendors vs ON vs.vendor_id=v.vendor')
		->join('LEFT join category c1 ON c1.category_id=p.product_class1')
		->join('LEFT join category c2 ON c2.category_id=p.product_class2')
		->join('LEFT join category c3 ON c3.category_id=p.product_class3')
		->join('LEFT join manufacturer m ON m.manufacturer_id=p.manufacturer_id')
		->group('p.product_id')
		->where($filter)
		->field('p.product_type,p.product_id,p.status,vs.lastname,vs.vendor_name,pd.name,p.item_no,p.packing_no,p.min_purchase_no,pov.quantity,v.product_cost,p.price,p.sale_price,p.model,p.product_code,p.sku,p.middle_package,p.length,p.height,p.width,p.weight,p.minimum,p.sort_order,p.date_added,pov.option_value_id,c1.code as product_class1,c2.code as product_class2,c3.code as product_class3,m.name as mname,ma.name as maname')
		->select();

		$product_data = array_merge($product_data1,$product_data2);
		return $product_data;
	}

	public function getParentdetail(){
		$sql = "SELECT  pd.`name`, pov.quantity, v.product_cost, p.price, p.product_code, pg.parent_product_code,pg.parent_product_id,pg.child_product_code FROM product_option_value pov LEFT JOIN product_group pg ON pg.parent_product_code = pov.product_code LEFT JOIN product p ON pov.product_id = p.product_id LEFT JOIN product_description pd ON pd.product_id = pov.product_id LEFT JOIN vendor v ON v.vproduct_id = pov.product_id LEFT JOIN vendors vs ON vs.vendor_id = v.vendor WHERE pg.parent_product_code IS NOT NULL AND pg.child_product_code IS NOT NULL GROUP BY 	pg.parent_product_code,pg.child_product_code";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pCode = substr($row['child_product_code'], 0, 10).$row['parent_product_code'];
            $ret[$pCode]['parstatus'] = $row['status'];
            $ret[$pCode]['parname'] = $row['name'];
            $ret[$pCode]['parent_product_code'] = max(0, $row['parent_product_code']);
            $ret[$pCode]['parquantity'] = max(0, $row['quantity']);
            $ret[$pCode]['parproduct_cost'] = max(0, $row['product_cost']);
            $ret[$pCode]['parprice'] = max(0, $row['price']);
            $ret[$pCode]['parent_product_id'] = max(0, $row['parent_product_id']);
        }

		return $ret;
	}


	public function getAllProductsDetailcombin(){


		$productoptionvalueModel1 = M('product_option_value');
		
		$product_data = $productoptionvalueModel1
		->alias('pov')
		->join('LEFT join option_description od ON od.option_id=pov.option_id')
		->join('LEFT join product_group pg ON pg.child_product_code=pov.product_code')
		->join('LEFT join product p ON pov.product_id=p.product_id')
		->join('LEFT join product_description pd ON pd.product_id=pov.product_id')
		->join('LEFT join vendor v ON v.vproduct_id=pov.product_id')
		->join('LEFT join vendors vs ON vs.vendor_id=v.vendor')
		->join('LEFT join option_value_description ovd ON ovd.option_value_id=pov.option_value_id')
		->join('LEFT join category c1 ON c1.category_id=p.product_class1')
		->join('LEFT join category c2 ON c2.category_id=p.product_class2')
		->join('LEFT join category c3 ON c3.category_id=p.product_class3')
		->join('LEFT join manufacturer m ON m.manufacturer_id=p.manufacturer_id')
		->where('pg.parent_product_code IS NOT NULL AND pg.child_product_code IS NOT NULL ')
		->group('pg.parent_product_code,pg.child_product_code')
		->field('pov.option_value_id,pov.product_id,p.status,vs.lastname,vs.vendor_name,pd.name,p.p_type,p.is_single,is_part,p.item_no,p.packing_no,p.min_purchase_no,pov.quantity,v.product_cost,p.price,p.model,p.product_code,p.sku,p.middle_package,p.length,p.height,p.width,p.weight,p.minimum,p.sale_price,p.sort_order,p.date_added,p.clearance,ovd.name as ovd_name,c1.code as product_class1,c2.code as product_class2,c3.code as product_class3,m.name as mname,pg.*')
		->order('pg.product_group_id desc ')
		->select();
		// echo $productoptionvalueModel1->getLastsql();die();

		return $product_data;
	}
	public function getAllProductsDetailSku($filter){
		$productoptionvalueModel = M('product');
		$product_data = $productoptionvalueModel
		->alias('p')
		->join('LEFT join product_description pd ON pd.product_id=p.product_id')
		->join('LEFT join vendor v ON v.vproduct_id=p.product_id')
		->join('LEFT join vendors vs ON vs.vendor_id=v.vendor')
		->join('LEFT join product_to_category ptc ON ptc.product_id=p.product_id')
		->join('LEFT join category_description cd ON cd.category_id=ptc.category_id')
		->group('p.product_id')
		->where($filter)
		->field('p.product_id,p.status,vs.lastname,vs.vendor_name,pd.name,p.item_no,p.packing_no,p.min_purchase_no,p.quantity,v.product_cost,p.price,p.product_code,p.sku,p.length,p.height,p.width,p.weight,p.minimum,p.sort_order,p.date_added,cd.name as cd_name')
		->select();
		return $product_data;
	}

	//过滤空格之类不可见的字符串
	public function trim_all_arr($data){
		$newdata = $data;
		foreach($newdata as $key=>$val){
			if($newdata[$key]!=null)
				$newdata[$key] = trim($val);
		}
		return $newdata;
	}
	
	//获取商品编码是否唯一性
	public function getProductByCode($product_code,$product_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "product WHERE product_id !='".$product_id."' AND LCASE(product_code) = '" . $this->db->escape(utf8_strtolower($product_code)) . "'");

		return $query->row;
	}

    /*
     * 维护销售组合时的简单产品筛选功能
     * @author sonicsjh
     * 暂时只支持到product表的product code字段
     * 20171201修正，禁止商品组嵌套
     */
    public function productSearchForGroup($filter, $page=1, $offset=10) {
        $sql = "
        SELECT 
            P.`product_id`,P.`product_code`,P.`image`,P.`price`,P.`sale_price`,P.`status`,
            PD.`name`,
            V.`product_cost`
        FROM
            `".DB_PREFIX."product` AS P
            LEFT JOIN `".DB_PREFIX."product_description` AS PD ON (P.`product_id`=PD.`product_id`)
            LEFT JOIN `".DB_PREFIX."vendor` AS V ON (P.`product_id`=V.`vproduct_id`)
        WHERE
            P.`product_id`!='".$filter['thisProduct']."' AND 
            P.`product_type`!=2 AND
            PD.`language_id`='".(int)$this->config->get('config_language_id')."'
        ";

        if ('' != $filter['name'])
            $sql .= " AND PD.`name` LIKE '%".$filter['name']."%'";
        if ('' != $filter['code'])
            $sql .= " AND P.`product_code` LIKE '%".$filter['code']."%'";
        if ('' != $filter['sku'])
            $sql .= " AND P.`sku` LIKE '%".$filter['sku']."%'";
        $sql .= " ORDER BY P.`product_id` DESC";
        $sql .= " LIMIT ".($page-1)*$offset.",".$offset;

        $query = $this->db->query($sql);
        return $query->rows;
    }

    /*
     * 维护 product_group 表数据
     * @author sonicsjh
     * 暂时只支持到product表的product code字段
     */
    protected function _initProductGroupInfo($parentProductId, $postData) {
    	// var_dump($parentProductId);
    	// var_dump($postData);
    	// die();
        $parentProductCode = $postData['product_code'].'0';//商品页面用10位的，保存数据库用11位，末尾加0
        if ($parentProductId < 1){
            return false;
        }
        if ($postData['product_type'] != 2 || count($postData['groupChild']) < 1) {
            $this->_cleanProductGroupByParentProductId($parentProductId);
            return false;
        }
        $productGroupPKs = $sql = array();
        $c = 1;
        foreach ($postData['groupChild'] as $key=>$quantity){
            $temp = explode('_', $key);
            $productGroupPK = (int)$temp[0];
            $childProductId = (int)$temp[1];
            $childProductCode = $temp[2].'0';//商品页面用10位的，保存数据库用11位，末尾加0
            $quantity = max(1, (int)$quantity);
            if (0 == $childProductId || '' == $childProductCode){
                continue;
            }
            if ($productGroupPK){
                $productGroupPKs[] = $productGroupPK;
                $sql[] = "UPDATE `".DB_PREFIX."product_group` SET `child_quantity`='".$quantity."',`display_order`='".$c."' WHERE `product_group_id`='".$productGroupPK."'";
            }else{
                $sql[] = "INSERT INTO `".DB_PREFIX."product_group` SET `parent_product_id`='".$parentProductId."',`parent_product_code`='".$parentProductCode."',`child_product_id`='".$childProductId."',`child_product_code`='".$childProductCode."',`child_quantity`='".$quantity."',`display_order`='".$c."'";
            }
            $c++;
        }


        if (count($productGroupPKs)){
            $sqlClean = "DELETE FROM `".DB_PREFIX."product_group` WHERE `product_group_id` NOT IN (".implode(',', $productGroupPKs).") AND `parent_product_id`='".$parentProductId."'";
            $this->db->query($sqlClean);
        }else{
            $this->_cleanProductGroupByParentProductId($parentProductId);
        }
        foreach ($sql as $s) {
            $this->db->query($s);
        }

        foreach ($postData['groupChildPrice'] as $key=>$price){
            $temp = explode('_', $key);
            $productGroupPK = (int)$temp[0];
            $childProductId = (int)$temp[1];
            $childProductCode = $temp[2].'0';//商品页面用10位的，保存数据库用11位，末尾加0
            $price = max(1, (int)$price);
            if (0 == $childProductId || '' == $childProductCode){
                continue;
            }
            if ($productGroupPK){
            $productGroupPKs[] = $productGroupPK;
            $sql1[] = "UPDATE `".DB_PREFIX."product_group` SET `child_price`='".$price."' WHERE `product_group_id`='".$productGroupPK."'";
            }else{
            	$sqlC = "select product_group_id from product_group where `parent_product_id`='".$parentProductId."'
            	 AND child_product_id=".$childProductId;
            	 $query = $this->db->query($sqlC);

                $sql1[] = "UPDATE `".DB_PREFIX."product_group` SET `child_price`='".$price."' WHERE `product_group_id`='".$query->row['product_group_id']."'";
            }
        }
        // var_dump($sql1);
        // var_dump($sqlC);
        // die();
        foreach ($sql1 as $s1) {
            $this->db->query($s1);
        }

    }

    /*
     * 根据 parent_product_id 清空 product_group 表相关数据
     * @author sonicsjh
     */
    protected function _cleanProductGroupByParentProductId($parentProductId){
        $sql = "DELETE FROM `".DB_PREFIX."product_group` WHERE `parent_product_id`='".$parentProductId."'";
        $this->db->query($sql);
    }

    /*
     * 根据 parent_product_id 获取 product_group 表数据，格式化后返回
     * @author sonicsjh
     */
    protected function _getGroupChildByParentProductId($parentProductId, $copyProduct=false) {
        $ret = array();
        if ($parentProductId){
            $sql = "SELECT `product_group_id`,`child_product_id`,`child_product_code`,`child_quantity`,`child_price` FROM `".DB_PREFIX."product_group` WHERE `parent_product_id`='".$parentProductId."' ORDER BY `display_order` ASC";
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $pCode = substr($row['child_product_code'], 0, 10);//数据库读取11位，商品页面用10位的，截取前10位
                if ($copyProduct){
                    $ret['_'.$row['child_product_id'].'_'.$pCode]['child_quantity'] = $row['child_quantity'];
                    $ret['_'.$row['child_product_id'].'_'.$pCode]['child_price'] = $row['child_price'];
                }else{
                    $ret[$row['product_group_id'].'_'.$row['child_product_id'].'_'.$pCode]['child_quantity'] = $row['child_quantity'];
                    $ret[$row['product_group_id'].'_'.$row['child_product_id'].'_'.$pCode]['child_price'] = $row['child_price'];
                }
            }
        }
        return $ret;
    }

    public function getChildProductsInGroup($groupChild) {
        $ret = array();
        if (count($groupChild)) {
            foreach ($groupChild as $key=>$quantity) {
            	// var_dump($groupChild);
                $temp = explode('_', $key);
                $childProductId = (int)$temp[1];
                $childProductCode = $temp[2];
                /*
                $sql = "
                SELECT 
                    P.`product_code`,PD.`name`,P.`image`,P.`price`,P.`status`,V.`product_cost`,PS.`price` AS special_price 
                FROM 
                    `".DB_PREFIX."product` AS P 
                    LEFT JOIN `".DB_PREFIX."product_description` AS PD ON (P.`product_id`=PD.`product_id`) 
                    LEFT JOIN `".DB_PREFIX."product_special` AS PS ON (P.`product_id`=PS.`product_id`) 
                    LEFT JOIN `".DB_PREFIX."vendor` AS V ON (P.`product_id`=V.`vproduct_id`) 
                WHERE 
                    P.`product_id`='".$childProductId."' AND 
                    PS.`customer_group_id`='".(int)$this->config->get('config_customer_group_id')."' AND
                    PD.`language_id`='".(int)$this->config->get('config_language_id')."'
                ";
                */
                $sql = "
                SELECT 
                    P.`product_code`,P.`image`,P.`price`,P.`sale_price`,P.`status`,
                    PD.`name`,
                    V.`product_cost` 
                FROM 
                    `".DB_PREFIX."product` AS P 
                    LEFT JOIN `".DB_PREFIX."product_description` AS PD ON (P.`product_id`=PD.`product_id`) 
                    LEFT JOIN `".DB_PREFIX."vendor` AS V ON (P.`product_id`=V.`vproduct_id`) 
                WHERE 
                    P.`product_id`='".$childProductId."' AND 
                    PD.`language_id`='".(int)$this->config->get('config_language_id')."'
                ";
                // echo $sql;
                $query = $this->db->query($sql);
                $specialPrice = $this->getSpecialPrice($childProductId);
                if ($specialPrice <= 0) {
                    $specialPrice = $query->row['sale_price'];
                }


                /*
                $s = "SELECT `price` FROM `product_special` WHERE `product_id`='".$childProductId."' ORDER BY ";
                $q = $this->db->query($s);
                $defaultSpecialPrice = 0;
                $specialPrice = 0;
                foreach ($q->rows as $r) {
                    if ('' == $r['date_start'] && '' == $r['date_end']) {
                        $defaultSpecialPrice = $r['price'];
                    }else{
                        $dateStart = strtotime($r['date_start'].' 00:00:00');
                        $dateEnd = strtotime($r['date_end'].' 23:59:59');
                        $now = time();
                        if ($dateStart < $now && $now < $dateEnd) {
                            $specialPrice = $r['price'];
                        }
                    }
                }
                if ($specialPrice == 0) {
                    $specialPrice = $defaultSpecialPrice;
                }
                */
                $ret[] = array(
                    'key' => $key,
                    'sku' => $childProductId.'_'.$childProductCode,
                    'image' => $query->row['image'],
                    'name' => $query->row['name'],
                    'code' => $query->row['product_code'],
                    'status' => $query->row['status'],
                    'product_price' => round($query->row['price'], 2),
                    'product_cost' => round($query->row['product_cost'], 2),
                    'product_special_price' => round($specialPrice, 2),
                    'quantity' => $quantity['child_quantity'],
                    'child_price' => $quantity['child_price'],
                );
            }
        }
        // var_dump($ret);
        return $ret;
    }
	
	//获取商品分类
	public function getCategoryClass($parent_id){

		$class = M('category')
			->alias('c')
			->join('category_description cd on c.category_id = cd.category_id','left')
			->field('cd.name,c.*')
			->where('c.code != "" AND c.status = 1 AND c.parent_id = '.$parent_id)->select();
		
		return $class;

	}
	
	//计算该分类已有多少商品
	public function searchCode($classCode){

		$lastcode = M("product")
			->where("left(product_code,6) =".$classCode)->order('product_code desc')->limit(1)->getField('product_code');
		return $lastcode;

	}

    /*
     * 获取所有商品数据，返回格式满足 po_product 表格式
     * @author sonicsjh
     */
    public function getInfoIntoPoProductByProductCode($productCode) {
        $sql = "
        SELECT
            POV.`product_code` AS productCode,
            POV.`product_id` AS productId,
            PD.`name` AS productName,
            P.`sku` AS productSku,
            V.`product_cost` AS productCost,
            VS.`user_id` AS vUserId,
            P.`packing_no` AS productPackingNo,
            POV.`product_option_value_id` AS productOptionId,
            OVD.`name` AS productOptionName
        FROM
            `".DB_PREFIX."product_option_value` AS POV
            LEFT JOIN `".DB_PREFIX."product` AS P ON (POV.`product_id`=P.`product_id`)
            LEFT JOIN `".DB_PREFIX."product_description` AS PD ON (POV.`product_id`=PD.`product_id` AND PD.`language_id`='".(int)$this->config->get('config_language_id')."')
            LEFT JOIN `".DB_PREFIX."vendor` AS V ON (POV.`product_id`=V.`vproduct_id`)
            LEFT JOIN `".DB_PREFIX."option_value_description` AS OVD ON (POV.`option_value_id`=OVD.`option_value_id` AND OVD.`language_id`='".(int)$this->config->get('config_language_id')."')
            LEFT JOIN `".DB_PREFIX."vendors` AS VS ON (VS.`vendor_id`=V.`vendor`)
        WHERE
            POV.`product_code`='".$productCode."';
        ";
        $query = $this->db->query($sql);
        return $query->row;

        foreach ($query->rows as $row) {
            $ret[$row['productCode']] = $row;
        }
        return $ret;
    }

    /*
     * 根据商品编号，客户组编号，时间，得到实际销售价格
     */
    public function getSpecialPrice($productId, $customerGroupId=false, $now=false) {
        if (false === $customerGroupId) {
            $customerGroupId = 1;
        }
        if (false === $now) {
            $now = date('Ymd');
        }
        $ret = 0;
        if ($productId) {
            /*
            $sql = "SELECT `price`,`date_start`,`date_end` FROM `".DB_PREFIX."product_special` WHERE `product_id`='".$productId."' AND `customer_group_id`='".$customerGroupId."' ORDER BY `product_special_id` DESC";
            foreach ($query->rows as $row) {
                $start = intval(str_replace('-', '', $row['date_start']));
                $end = intval(str_replace('-', '', $row['date_end']));
                if ((0 == $start && 0 == $end) || ($now >= $start && $now <= $end) || (0 == $start && $now <= $end) || ($now >= $start && 0 == $end)) {
                    $ret = round($row['price'], 2);
                    break;
                }
            }
            */
            $sql = "SELECT `price` FROM `".DB_PREFIX."product_special` WHERE `product_id`='".$productId."' AND `customer_group_id`='".$customerGroupId."' AND ((`date_start`=0 OR `date_start`<='".$now."') AND (`date_end`=0 OR `date_end`>='".$now."')) ORDER BY `priority` ASC,`price` ASC";
            $query = $this->db->query($sql);
            $ret = round($query->row['price'], 2);
        }
        return $ret;
    }
    //查找userGroup($this->user->getId())

    public function userGroup($user){
    	$sql = "SELECT `user`.user_group_id FROM `user` where `user_id` = ".$user;
    	$query = $this->db->query($sql);
        return $query->row['user_group_id'];
	}
	//上下架

	public function changeStatus($product_id,$op_id){
    	$this->db->query("UPDATE " . DB_PREFIX . "product SET status = '" . $op_id . "' WHERE product_id = '" . (int)$product_id . "'");
        return true;
	}
	
	//添加分配区域
	public function addArea($product_id,$data){
		
		$add['product_id'] = $product_id;
		
		if(isset($data['only_countries'])){

			foreach($data['only_countries'] as $key=>$value){
				if($key == count($data['only_countries'])-1){
					$add['only_countries'].=$value;
				}
				else{
					$add['only_countries'].=$value.",";
				}
			}
		}

		if(isset($data['only_zones'])){

			foreach($data['only_zones'] as $key=>$value){
				if($key == count($data['only_zones'])-1){
					$add['only_zones'].=$value;
				}
				else{
					$add['only_zones'].=$value.",";
				}
			}
		}

		if(isset($data['no_countries'])){

			foreach($data['no_countries'] as $key=>$value){
				if($key == count($data['no_countries'])-1){
					$add['no_countries'].=$value;
				}
				else{
					$add['no_countries'].=$value.",";
				}
			}
		}

		if(isset($data['no_zones'])){

			foreach($data['no_zones'] as $key=>$value){
				if($key == count($data['no_zones'])-1){
					$add['no_zones'].=$value;
				}
				else{
					$add['no_zones'].=$value.",";
				}
			}
		}

		M('product_to_area')->add($add);

	}

	public function getAgvBuyByid($product_id)
    {
        $sql = "SELECT po_product.product_id, SUM(po_product.unit_price * po_product.delivered_qty ) / SUM(po_product.delivered_qty) AS 'buyav'FROM po_product WHERE po_product.product_id = ".$product_id;
        $query = $this->db->query($sql);
        $buyav = $query->row['buyav'];
        // if (empty($query->row['buyav'])) {
        	
        // }
        return $buyav;
    } 
	
	//查看是否是组合商品内商品
	public function checkChild($product_id){
		
		$where['child_product_id'] = $product_id;
		$check = M('product_group')->where($where)->count();

		if($check != 0){
			return 1;
		}
		else{
			return 0;
		}

	}

	public function gettime($product_code){
		$product_code = $product_code.'0';
		$sql = "SELECT p.id,p.date_added, pp.product_id, pp.product_code FROM po_product AS pp INNER JOIN po AS p ON pp.po_id = p.id WHERE pp.product_code = ".$product_code." group BY p.id";
		// echo $sql;
		$query = $this->db->query($sql);
		$po = $query->rows ;
		if (empty($po)) {
			return;
		}
     	$sql = "SELECT sd.product_code, s.date_added, s.refer_id, s.refer_type_id FROM stock_in AS s INNER JOIN stock_in_detail AS sd ON s.in_id = sd.in_id where s.refer_type_id=2 AND sd.product_code = ".$product_code; 
     	$query = $this->db->query($sql);
     	$st = $query->rows ;
     	if (empty($st)) {
			return;
		}
     	// echo $sql;
     	$i = 0;
     	$time  = 0;
     	foreach ($po as $key => $value) {
     		foreach ($st as $key1 => $value1) {
     			if ($value1['refer_id'] == $value['id'] ) {
     				$time += strtotime($value1['date_added']) - strtotime($value['date_added']) ;
     				$i++;
     			}
     		}
     	}
		$time = $time/($i*60*60*24) ;
		return $time;

	}
	public function getShelf($product_id){
		$product_code = M('product')->where(array('product_id'=>$product_id))->getField('product_code');
		$product_code = $product_code.'0';
		$sql = "SELECT s.solution_name, sf.shelf_name, sf.shelf_id, sp.product_code FROM solution AS s LEFT JOIN shelf AS sf ON s.solution_id = sf.solution_id LEFT JOIN shelf_product AS sp ON sf.shelf_id = sp.shelf_id WHERE s.is_template=1 AND sp.product_code =".$product_code." group BY sf.shelf_id";
		$query = $this->db->query($sql);
		$str = '';
       foreach ($query->rows as $key => $value) {
       		if (!empty($value['solution_name'])) {
       			$str .= ','.$value['solution_name'].'_'.$value['shelf_name'];
       		}
        }
        return $str;

	}

	//前后台同步
	public function getclearqty($id){
		$sql = "SELECT SUM(i.available_quantity) as 'clearqty',pv.product_code FROM inventory AS i LEFT JOIN product_option_value AS pv ON pv.product_code = i.product_code LEFT JOIN warehouse AS w ON i.warehouse_id = w.warehouse_id WHERE i.`status` = 1 AND w.type!=3 AND pv.product_id=  ".$id;
		// echo $sql;
        $query = $this->db->query($sql);
       return $query->row;


        // $sql = "UPDATE `".DB_PREFIX."product_option_value` SET `quantity`=".$clearqty." WHERE `product_id`='".$id."'";
        // echo M('product_option_value')->getlastsql();die;
        // $this->db->query($sql);
         // return $clearqty;
         // var_dump($clearqty) ;die(); 
    }

    public function checkProductCodeExists($productCode) {
        $sql = "SELECT `product_id` FROM `product_option_value` WHERE `product_code`='".$productCode."'";
        $query = $this->db->query($sql);
        return intval($query->row['product_id']);
    }

}
